package com.sql.test.service;

import android.content.Context;
import android.util.Log;
import android.widget.EditText;
import android.widget.Toast;

import com.R;
import com.sql.test.utilities.ActivityHelper;
import com.sql.test.utilities.AssetHelper;

import java.io.File;
import java.io.IOException;
import java.util.ArrayList;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import gov.nasa.worldwind.geom.Vec3;
import jsqlite.Constants;
import jsqlite.Database;
import jsqlite.Stmt;

/**
 * Created by Lenovo on 2018/4/22.
 */

public class LocalDatabaseSerivce  implements DatabaseService{
    private static  final String TAG = "InsertDB";
    private static  jsqlite.Database db = new jsqlite.Database();

    private String geometryColumn = null;
    //id字段具有特殊性，用户不能自定义取值，故而需特殊处理
    private ArrayList<String> idColumn = null;

    public ArrayList<String> getIdColumn() {
        return idColumn;
    }
    public String getGeometryColumn() {
        return geometryColumn;
    }
    public   Database getDb() {
        return db;
    }

    /**
     * 加载本地数据库，将数据库文件复制到指定路径
     * */
    public   void loadLocalDB(Context context){
        try {
            //step 1:check and copy dbfile
            File dbFile = new File( context.getExternalFilesDir(null).toString()+"/"+context.getString(R.string.local_db));
            if(!dbFile.exists()) {
                ActivityHelper.showAlert(context,
                        "dbFile_location:"+context.getExternalFilesDir(null).toString());
                AssetHelper.CopyAsset(context,
                        ActivityHelper.getPath(context, true),
                        context.getString(R.string.local_db));
            }
        }catch(IOException e){
            ActivityHelper.showAlert(context,"Failed to copy db to external");
        }
    }

    public  void  connectlocalDB(Context context, int openmode){
        try {
            String dbFile = ActivityHelper.getDataBase(context,
                    context.getString(R.string.local_db));
            if (dbFile == null) {
                ActivityHelper.showAlert(context,
                        context.getString(R.string.error_locate_failed));
                throw new IOException(context.getString(R.string.error_locate_failed));
            }
            db.open(dbFile.toString(), openmode);

        } catch (jsqlite.Exception e) {
            Log.e(TAG, e.getMessage());
        } catch (IOException e) {
            Log.e(TAG, e.getMessage());
        }
    }
    public  int getRecordsNumber(Context context,String tableName) {
        int count = 0;
        try {
            connectlocalDB(context, Constants.SQLITE_OPEN_READWRITE);
            Stmt stmt = db.prepare("SELECT COUNT(*) FROM "+tableName+";");
            while (stmt.step()) {
                count = stmt.column_int(0);
            }
            db.close();
            return count;
        } catch (jsqlite.Exception e) {
            Log.e(TAG, e.getMessage());
        }
        return -1;
    }
    public  boolean insertLocalDB(Context context,String name,String type ,String description,String geometry){

        try {
            connectlocalDB(context,Constants.SQLITE_OPEN_READWRITE|Constants.SQLITE_OPEN_CREATE);

            Stmt stmt01 = db
                    .prepare("INSERT INTO test_geom (name, type, description, the_geom) VALUES (?,?,?,GeomFromText(?, 4326));");
            stmt01.bind(1, name);
            stmt01.bind(2, type);
            stmt01.bind(3,description);
            stmt01.bind(4,geometry);
            stmt01.step();

            stmt01.clear_bindings();
            stmt01.reset();
            stmt01.close();
            db.close();
            return  true;
        } catch (jsqlite.Exception e) {
            Log.e(TAG, e.getMessage());
        }catch (Exception e){
            Log.e(TAG, e.getMessage());
        }
        return false;
    }

    private  void insertSpatialData() throws Exception {
        Stmt stmt01 = db
                .prepare("INSERT INTO test_geom (name, measured_value, the_geom) VALUES (?,?,GeomFromText(?, 4326));");
        stmt01.bind(1, "first point");
        stmt01.bind(2, 1.23456);
        stmt01.bind(3, "POINT(1.01 2.02)");
        stmt01.step();

        stmt01.clear_bindings();
        stmt01.reset();

        stmt01.bind(1, "second point");
        stmt01.bind(2, 2.34567);
        stmt01.bind(3, "POINT(2.02 3.03)");
        stmt01.step();

        stmt01.clear_bindings();
        stmt01.reset();

        stmt01.bind(1, "third point");
        stmt01.bind(2, 3.45678);
        stmt01.bind(3, "POINT(3.03 4.04)");
        stmt01.step();

        stmt01.close();
    }

    public  void CreateSpatialTableWithIndex(Context context) throws Exception {

        connectlocalDB(context,Constants.SQLITE_OPEN_READWRITE|Constants.SQLITE_OPEN_CREATE);

        db.spatialite_create();

        db.exec("CREATE TABLE test_geom (fid INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, name TEXT NOT NULL, type TEXT NOT NULL, description TEXT);",
                null);

        db.exec("SELECT AddGeometryColumn('test_geom', 'the_geom', 4326, 'POINT', 'XY');",
                null);

        db.exec("SELECT CreateSpatialIndex('test_geom', 'the_geom');", null);

        //add table into geometry_columns,keep we can get table from geometry_columns
        //geometry_columns:f_table_name,f_geometry_column,geometry_type,coord_dimension,srid,spatial_index_enabled
        db.exec("insert into geometry_columns values('test_geom','the_geom',1,2,4326,1)",null);

    }




    public  ArrayList<String> selectTablesName(Context context){
        try {
            ArrayList<String> tables = new ArrayList<>();
            connectlocalDB(context, Constants.SQLITE_OPEN_READWRITE | Constants.SQLITE_OPEN_CREATE);
            //String sql = "PRAGMA stats;";
            String sql = "SELECT f_table_name FROM geometry_columns;";
            Stmt stmt01 = db.prepare(sql);
            //columns:table index width height
            while (stmt01.step()) {
                tables.add(stmt01.column_string(0));
            }
            db.close();
            return tables;
        }catch (jsqlite.Exception e){
            e.printStackTrace();
        }
        return null;
    }

    public  ArrayList<String> selectCoulmnsName(Context context,String selectedTableName){
        try {
            //use to get idColumn if exist
            idColumn = new ArrayList<>();

            ArrayList<String> columns = new ArrayList<>();
            connectlocalDB(context, Constants.SQLITE_OPEN_READWRITE | Constants.SQLITE_OPEN_CREATE);
            String sql = "PRAGMA table_info('"+selectedTableName+"')";
            Stmt stmt01 = db.prepare(sql);
            //columns :cid name type notnull dflt_value pk
            while (stmt01.step()) {
                //check type to get geometry column
                String type = stmt01.column_string(2);
                if(type.contains("POINT")||type.contains("LINE")||type.contains("POLYGON")){
                     geometryColumn = stmt01.column_string(1);
                }else {
                    columns.add(stmt01.column_string(1));
                }
                //只是记录是否存在id字段，不在插入字段中排除，所以，依旧添加在columns中作为查询条件字段,利用正则表达式进行匹配
                String regEx="id$|ID$"; //表示以id或ID结尾的字符串
                Pattern p=Pattern.compile(regEx);
                Matcher m=p.matcher(type);
                boolean result=m.find();
                if(result){
                    idColumn.add(stmt01.column_string(1));
                }
            }
            db.close();
            return columns;
        }catch (jsqlite.Exception e){
            e.printStackTrace();
        }
        return null;
    }

    public  ArrayList<String> selectColumnValues(Context context,String selectedTableName,String selectedColumnName){
        try {
            ArrayList<String> values = new ArrayList<>();
            connectlocalDB(context, Constants.SQLITE_OPEN_READWRITE | Constants.SQLITE_OPEN_CREATE);
            String sql = "SELECT "+selectedColumnName+" FROM " +selectedTableName;
            Stmt stmt01 = db.prepare(sql);
            while (stmt01.step()) {
                String value = stmt01.column_string(0);
                if(value==null){
                    value = "";
                }
                values.add(value);
            }
            db.close();
            return values;
        }catch (jsqlite.Exception e){
            e.printStackTrace();
        }
        return null;
    }

    public  ArrayList<String[]> selectFeatureData(Context context,String selectedTableName,String selectedColumnName,String selectedColumnValue,ArrayList<String> columns,String geometryColumn){
        try {

            //columns don't include geometry column
            ArrayList<String[]> featuresData = new ArrayList<>();
            String[] feature = null;
            connectlocalDB(context, Constants.SQLITE_OPEN_READWRITE | Constants.SQLITE_OPEN_CREATE);
            String columsString = columns.get(0);
            for(int i=1;i<columns.size();i++){
                columsString +=","+ columns.get(i);
            }
            columsString +=",ST_AsText("+geometryColumn+")";
            String sql = null;
            //支持查询所有记录，而不只是给定条件的记录
            if(!selectedColumnName.equals("")) {
                if(!selectedColumnValue.equals("")){
                    sql   = "SELECT " + columsString + " FROM " + selectedTableName + " WHERE " + selectedColumnName + " = '" + selectedColumnValue + "';";
                }else{
                    return null;
                }
            }else {
                sql   = "SELECT " + columsString + " FROM " + selectedTableName+" ;";
            }
            Stmt stmt01 = db.prepare(sql);
            while (stmt01.step()) {
                //此处实例化是开辟新的内存，避免一个对象频繁赋值引用，由于指向一个内存，之前的数据直接丢失
                feature = new String[columns.size()+1];
                for(int i=0;i<columns.size()+1;i++){
                    feature[i]=stmt01.column_string(i);
                }
                featuresData.add(feature);
            }
            db.close();
            return featuresData;
        }catch (jsqlite.Exception e){
            e.printStackTrace();
        }
        return null;
    }

    public ArrayList<String> selectTrigger(Context context,String tableName){
        //获取表中所有触发器：SELECT name FROM sqlite_master
        //WHERE type = 'trigger' and tbl_name = 'test_geom1'
        //由于从表geometry_columns获取的表名都被转化为小写，因此，为避免表名大小写带来表不存在的问题，所有表名都小写
        //SELECT name FROM sqlite_master WHERE type = 'trigger' and tbl_name = 'poiSet';";is ok
        //SELECT name FROM sqlite_master WHERE type = 'trigger' and tbl_name = 'poiset';";is wrong
        try {
            //columns don't include geometry column
            ArrayList<String> triggers = new ArrayList<>();
            connectlocalDB(context, Constants.SQLITE_OPEN_READWRITE | Constants.SQLITE_OPEN_CREATE);
            String sql = "SELECT name FROM sqlite_master WHERE type = 'trigger' and tbl_name = '"+tableName+"'";
            Stmt stmt01 = db.prepare(sql);
            while (stmt01.step()) {
                triggers.add(stmt01.column_string(0));
            }
            db.close();
            return triggers;
        }catch (jsqlite.Exception e){
            e.printStackTrace();
        }
        return null;
    }
    public boolean dropAllTrigger(Context context,String tableName) {
        try {
            ArrayList<String> triggersName = selectTrigger(context,tableName);
            //删除所有触发器：DROP TRIGGER triggerName
            //此处异常：jsqlite.Exception: unknown error in open,just only open in Constants.SQLITE_DROP_TRIGGER mode,so add Constants.SQLITE_OPEN_READWRITE
            connectlocalDB(context,Constants.SQLITE_OPEN_READWRITE|Constants.SQLITE_DROP_TRIGGER);
            for (int i = 0; i < triggersName.size(); i++) {
                String sql = "DROP TRIGGER " + triggersName.get(i);
                db.exec(sql, null);
            }
                db.close();
                return true;
            }catch(jsqlite.Exception e){
               //jsqlite.Exception: database already closed
                e.printStackTrace();
            }
            return false;
    }
    public  boolean insertFeatureData(Context context,String insertTableName,ArrayList<String> insertTableColumns,ArrayList<String> insertFeatureData,String geometryColumn){

        try {
            connectlocalDB(context,Constants.SQLITE_OPEN_READWRITE|Constants.SQLITE_OPEN_CREATE);
            String columnsString = insertTableColumns.get(0);
            String valueString = "'"+insertFeatureData.get(0)+"'";
            for(int i=1;i<insertTableColumns.size();i++) {
                columnsString += ","+insertTableColumns.get(i);
                valueString+=",'"+insertFeatureData.get(i)+"'";
            }
            columnsString+=","+geometryColumn;
            Stmt stmt01 = db
                 .prepare("INSERT INTO "+insertTableName+" ("+columnsString+") VALUES ("+valueString+",GeomFromText("+insertFeatureData.get(insertTableColumns.size())+", 4326));");
            //下面这种方式导致空间数据插入为空，后来发现不是，而是数据格式设的不对
            /*   for(int i=0;i<insertTableColumns.size();i++) {
                //bind索引位置从1开始，而不是0，否则提示错误信息：jsqlite.Exception: parameter position out of bounds
                stmt01.bind(i+1, insertFeatureData.get(i));
            }
            stmt01.bind(insertTableColumns.size()+1,insertFeatureData.get(insertTableColumns.size()));*/
            //执行插入时的问题：jsqlite.Exception: SQL logic error or missing database
            //可能的问题解释：一个sqlite3结构只能在调用 sqlite3_open创建它的那个进程中使用。
            // 你不能在一个线程中打开一个数据库然后把指针传递给另一个线程使用。
            // 这是因为大多数多线程系统的限制（或 Bugs？）例如RedHat9上。在这些有问题的系统上，
            // 一个线程创建的fcntl()锁不能由另一个线程删除或修改。由于SQLite依赖fcntl()锁来进行并发控制，
            // 当在线程间传递数据库连接时会出现严重的问题。
            //利用代码创建表就不会有问题，又是为什么？
            //发现代码创建的数据表并不包含将空间字段设置触发器，存入元数据表，设置空间索引等，所以，问题应该在空间属性上
            //发现空间字段显示上的区别在于是否insert into "geometry_columns" values('poiset','geometry',1,2,4326,1)
            //不过这句话还是没有影响,依旧可以成功插入
            /*CREATE TRIGGER "ggi_multiPoint_geom" BEFORE INSERT ON "multiPoint"
            FOR EACH ROW BEGIN
            SELECT RAISE(ROLLBACK, 'multiPoint.geom violates Geometry constraint [geom-type or SRID not allowed]')
            WHERE (SELECT geometry_type FROM geometry_columns
                    WHERE Lower(f_table_name) = Lower('multiPoint') AND Lower(f_geometry_column) = Lower('geom')
                    AND GeometryConstraints(NEW."geom", geometry_type, srid) = 1) IS NULL;
            END */
            //关键问题在于触发器，保持userData中的数据与MetaData，InternalData，Spatial Index
            //的一致性
            //解决方案：
            //获取表中所有触发器：SELECT name FROM sqlite_master
            //WHERE type = 'trigger' and tbl_name = 'test_geom1'
            //删除所有触发器：DROP TRIGGER triggerName
            stmt01.step();
            stmt01.clear_bindings();
            stmt01.reset();
            stmt01.close();
            db.close();
            return  true;
        } catch (jsqlite.Exception e) {
            Log.e(TAG, e.getMessage());
        }catch (Exception e){
            Log.e(TAG, e.getMessage());
        }
        return false;
    }

public boolean createTable(Context context,String tableName,String  geometryType,ArrayList<String[]> columnsInfo){
        try {

            if(tableName.equals("")|geometryType.equals("")){
                Toast.makeText(context,"表名或空间类型为空值！",Toast.LENGTH_SHORT).show();
                return  false;
            }
            if(columnsInfo.size()>0) {
                //保证空间类型大写格式，保证正确的语法要求，保证与输入，查询模块的一致性
                geometryType.toUpperCase();
                //生成创建表属性sql语句
                String columnInfoString = columnsInfo.get(0)[0] + " " + columnsInfo.get(0)[1];
                for (int i = 1; i < columnsInfo.size(); i++) {
                    //属性数据类型保持大写格式
                    columnInfoString += "," + columnsInfo.get(i)[0] + " " + columnsInfo.get(i)[1].toUpperCase();
                }
                //打开数据库
                connectlocalDB(context, Constants.SQLITE_OPEN_READWRITE | Constants.SQLITE_OPEN_CREATE);

                db.spatialite_create();
                //根据一般属性创建表
                db.exec("CREATE TABLE " + tableName + " (" + columnInfoString + ");", null);
                //添加空间属性字段
                db.exec("SELECT AddGeometryColumn('" + tableName + "', 'geom', 4326, '" + geometryType + "', 'XY');", null);
                //创建空间索引
                db.exec("SELECT CreateSpatialIndex('" + tableName + "', 'geom');", null);
                //add table into geometry_columns,keep we can get table from geometry_columns
                //geometry_columns:f_table_name,f_geometry_column,geometry_type,coord_dimension,srid,spatial_index_enabled
                //默认为点类型id
                int geometryTypeId = 1;
                switch (geometryType) {
                    case "POINT":
                        geometryTypeId = 1;
                        break;
                    case "LINESTRING":
                        geometryTypeId = 2;
                        break;
                    case "POLYGON":
                        geometryTypeId = 3;
                        break;
                }
                //"insert into geometry_columns values('tablename','geom',1,2,4326,1)"
                //table_name,geometry_name,geometry_type,coord_dimension,srid,spatial_index_enabled
                //插入该表的意义在于应用将通过该表查询当前数据库中有哪些空间表，获取表名以便进行相应操作
                db.exec("insert into geometry_columns values('" + tableName + "','geom'," + geometryTypeId + ",2,4326,1)", null);
                db.close();
                return true;
            }else{
                Toast.makeText(context,"至少添加一个属性！",Toast.LENGTH_SHORT).show();
                return  false;
            }
        } catch (jsqlite.Exception e){
            Toast.makeText(context,"建表失败："+e,Toast.LENGTH_SHORT).show();
            e.printStackTrace();
        }
     return  false;
}


//计算两点之间的距离,限于二维
    public double calculateXYDistance(Context context,String prePoint,String currentPoint){
            //prePoint:'POINT(x y)'
            double distance = 0;
            try{
                connectlocalDB(context,Constants.SQLITE_OPEN_READWRITE);
                String sql = "SELECT Distance(GeomFromText("+prePoint+",4326),GeomFromText("+currentPoint+",4326))";
                Stmt stmt = db.prepare(sql);
                while (stmt.step()){
                    distance = stmt.column_double(0);
                }
            }catch (Exception e){
                e.printStackTrace();
            }
            return distance;
    }

    //自定义三维空间点之间的距离
    public double calculateDistance(Vec3 prePoint,Vec3 currentPoint){
        //prePoint:'POINT(x y)'
        double distance = 0;
        if(prePoint!=null&&currentPoint!=null) {
            try {
                distance = Math.pow(Math.pow((currentPoint.x - prePoint.x), 2) + Math.pow((currentPoint.y - prePoint.y), 2) + Math.pow((currentPoint.z - prePoint.z), 2), 0.5);
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        return distance;
    }

}
